<?php

namespace app\admin\service;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use app\common\BaseHelper;

use app\admin\controller\Excel;

use app\admin\model\Povertypolicy as PovertypolicyMdl;
use app\admin\model\Povertypolicyledger;
use app\admin\model\Member;
use app\admin\model\Villages;
use app\admin\model\Area;

use think\Db;
use think\Exception;

class Povertypolicy
{

    /**
     * 下载Excel模板
     */
    public static function downloadTemplate($request){
        $param = $request->param();
        $townId = $param['townId'];

        $xlsTitle = 'Templete';
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $spreadsheet->getActiveSheet()->setTitle($xlsTitle);

        $spreadsheet->getActiveSheet()->setCellValue('A1', '政策享受明细');
        //$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

        $policyledgerInfo = Povertypolicyledger::where(['id' => $param['ledgerId']])->find();
        $policyList = json_decode($policyledgerInfo['policy'], true);

        $spreadsheet->getActiveSheet()->setCellValue('A2', '所属镇');
        $spreadsheet->getActiveSheet()->setCellValue('B2', '所属村');
        $spreadsheet->getActiveSheet()->setCellValue('C2', '户主名');
        $spreadsheet->getActiveSheet()->setCellValue('D2', '贫困户名');
        $spreadsheet->getActiveSheet()->setCellValue('E2', '台账ID('. $param['ledgerId'] .')');
        // 合并列
        $spreadsheet->getActiveSheet()->mergeCells('A2:A3');
        $spreadsheet->getActiveSheet()->mergeCells('B2:B3');
        $spreadsheet->getActiveSheet()->mergeCells('C2:C3');
        $spreadsheet->getActiveSheet()->mergeCells('D2:D3');
        $spreadsheet->getActiveSheet()->mergeCells('E2:E3');
        // 居中显示
        $spreadsheet->getActiveSheet()->getStyle('A2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $spreadsheet->getActiveSheet()->getStyle('B2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $spreadsheet->getActiveSheet()->getStyle('C2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $spreadsheet->getActiveSheet()->getStyle('D2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $spreadsheet->getActiveSheet()->getStyle('E2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        //$i = 5;
        $j = 5;
        foreach ($policyList as $policy) {
            if(count($policy['policySpec']) > 1){
                // 多列
                $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '2', show_policys($policy['policyType']));
                // 合并行
                $spreadsheet->getActiveSheet()->mergeCells(intToChr($j) . '2:' . intToChr($j + count($policy['policySpec']) - 1) . '2');
                // 居中显示
                $spreadsheet->getActiveSheet()->getStyle(intToChr($j) . '2')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                    ]
                ]);
                foreach($policy['policySpec'] as $policySpec){
                    switch($policySpec['specType']){
                        case 1:
                            $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '3', show_quarter($policySpec['quarter']));
                            break;
                        case 2:
                            $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '3', show_month($policySpec['month']));
                            break;
                        default: break;
                    }
                    $j ++;
                }
            }else{
                // 一列
                $policySpec = $policy['policySpec'][0];
                if($policySpec['specType'] != 0){
                    switch($policySpec['specType']){
                        case 1:
                            $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '3', show_quarter($policySpec['quarter']));
                            break;
                        case 2:
                            $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '3', show_month($policySpec['month']));
                            break;
                        default: break;
                    }
                }else{
                    // 合并列
                    $spreadsheet->getActiveSheet()->mergeCells(intToChr($j) .'2:'. intToChr($j) .'3');
                    // 居中显示
                    $spreadsheet->getActiveSheet()->getStyle(intToChr($j) . '2')->applyFromArray([
                        'alignment' => [
                            'horizontal' => Alignment::HORIZONTAL_CENTER,
                            'vertical' => Alignment::VERTICAL_CENTER
                        ]
                    ]);
                }
                $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '2', show_policys($policy['policyType']));
                $j ++;
            }
            //$i ++;
        }
        $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '2', '备注');
        // 合并列
        $spreadsheet->getActiveSheet()->mergeCells(intToChr($j) .'2:'. intToChr($j) .'3');
        // 居中显示
        $spreadsheet->getActiveSheet()->getStyle(intToChr($j) .'2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);

        // 合并
        $spreadsheet->getActiveSheet()->mergeCells('A1:' . intToChr($j). '1');
        // 将A1单元格设置为水平居中对齐
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
        // 添加边框
        $styleArray2 = [
            'borders' => [
                'outline' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A2:'. intToChr($j). '3')->applyFromArray($styleArray2);


        $filePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'csv' . DS . 'povertypolicy' . DS . 'templete' . DS . date('Y-m-d');
        $fileName = $filePath . '/povertypolicy_templete_' . date('y-m-d-H-i-s');

        //检查文件或目录是否存在
        if(!file_exists($filePath)){
            mkdir($filePath, 0777, true);
        }
        $writer = new Xlsx($spreadsheet);
        //$writer->save($fileName . '.xls'); // or .xlsx

        $xlsTitle = iconv('utf-8', 'gb2312', $xlsTitle); // 文件名称
        $downFileName = basename($fileName,".".pathinfo($fileName, PATHINFO_EXTENSION));
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$downFileName.xls"); // attachment 新窗口打印,inline 本窗口打印

        $writer->save('php://output');
        exit;
    }

    /**
     * 导入数据
     */
    public static function import(){
        $session_admin = session('ADMIN');
        $file = request()->file('csv_file');
        $model = new PovertypolicyMdl();

        Db::startTrans();
        try {
            $filePath = ROOT_PATH . 'public' . DS . 'upload';
            $info = $file->move($filePath);
            $fileName = $info->getSaveName();
            $fileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($filePath . DS . $fileName);
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($fileType);
            // 设置编码
            if(strtolower($fileType) == 'csv'){
                $reader->setInputEncoding('CP936'); // 只对csv文件有效
            }
            $spreadsheet = $reader->load($filePath . DS . $fileName);

            $sheetData = $spreadsheet->getActiveSheet()->toArray();
            if(count($sheetData) <= 3){
                return json(['status' => 0, 'msg' => '未找到文件内容，请填写数据。']);
            }

            foreach ($sheetData as $key => $val) {
                if($key <= 2){
                    continue;
                }

                $townId = Area::where(['name' => $val[0]])->value('id');
                if(!$townId){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '未找到' . $val[0]]);
                }
                $areaIds = action('admin/Base/getParentIdsByTownId', ['townId' => $townId]);
                $cityId = $areaIds['cityId'];
                $xianId = $areaIds['xianId'];
                if($val[1]){
                    $villageId = Villages::where(['name' => $val[1]])->value('id');
                    if(!$villageId){
                        Db::rollback();
                        return json(['status' => 0, 'msg' => '未找到' . $val[1]]);
                    }
                }else{
                    $villageId = 0;
                }

                // 必填字段 验证
                if(!$val[2] || !$val[3] || !$val[4]){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '户主名、贫困户名等是必填字段']);
                }

                // 检查户主
                $householderIdInfo = Member::where(['name' => $val[2], 'isDelete' => 2])->field('id, name')->find();
                if(!$householderIdInfo){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '户主 '. $val[2] .' 不存在！']);
                }
                $householderId = $householderIdInfo['id'];
                // 检查贫困户
                $memberInfo = Member::alias('a')->where(['a.name' => $val[3], 'a.isDelete' => 2])->join('__MEMBEREXTRAINFO__ e', 'a.id = e.memberId')->field('a.id, a.name, e.isHouseholder, e.householderId')->find();
                if(!$memberInfo){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '贫困户 '. $val[3] .' 不存在！']);
                }
                if($memberInfo['isHouseholder'] == 1){
                    // 是户主
                    if($val[3] != $val[2]){
                        Db::rollback();
                        return json(['status' => 0, 'msg' => '贫困户 '. $val[3] .' 与户主 ' . $val[2] .' 关系有误！']);
                    }
                }else{
                    // 不是户主
                    if($memberInfo['householderId'] != $householderIdInfo['id']){
                        Db::rollback();
                        return json(['status' => 0, 'msg' => '贫困户 '. $val[3] .' 与户主 ' . $val[2] .' 关系有误！']);
                    }
                }
                $memberId = $memberInfo['id'];
                // 检查台账
                $povertypolicyledgerInfo = Povertypolicyledger::where(['id' => $val[4]])->field('id, policy')->find();
                if(!$povertypolicyledgerInfo){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '台账 '. $val[4] .' 不存在！']);
                }
                $policyledgerId = $val[4];
                // 贫困户是否已添加了政策享受
                $povertypolicyInfo = $model->where(['policyledgerId' => $policyledgerId, 'memberId' => $memberId])->field('memberId')->find();
                if($povertypolicyInfo){
                    //Db::rollback();
                    //return json(['status' => 0, 'msg' => $val[3] .' 已添加过此政策享受！']);
                    continue;
                }
                // 享受政策
                $i = 5;
                $policy = [];
                $policyList = json_decode($povertypolicyledgerInfo['policy'], true);
                foreach ($policyList as $policys) {
                    $policyType = $policys['policyType'];
                    $policySpec = [];
                    foreach ($policys['policySpec'] as $key => $value) {
                        // 享受政策规格
                        $policySpec[] = [
                            'specType' => $value['specType'],
                            'quarter' => $value['quarter'],
                            'month' => $value['month'],
                            'explain' => $value['explain'],
                            'explainValue' => $val[$i],
                        ];
                        $i ++;
                    }
                    $policy[] = [
                        'policyType' => $policyType,
                        'policySpec' => $policySpec
                    ];
                }
                $policy = json_encode($policy);
                // 备注
                $remark = $val[$i];

                $data = [
                    'id'                 => BaseHelper::getUUID(),
                    'cityId'             => $cityId,
                    'xianId'             => $xianId,
                    'townId'             => $townId,
                    'villageId'          => $villageId,
                    'householderId'      => $householderId,
                    'memberId'           => $memberId,
                    'policyledgerId'     => $policyledgerId,
                    'policy'             => $policy,
                    'remark'             => $remark,
                    'createDate'         => time(),
                    'createOper'         => $session_admin->id,
                ];

                $result = $model->insert($data);
                if($result === false){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '导入失败！']);
                }
            }
            @unlink($filePath . DS . $fileName);

            Db::commit();
            return json(['status' => 1, 'msg' => '导入成功！']);
        } catch (\Exception $e){
            Db::rollback();
            //$msg = $e->getMessage();
            $msg = '导入失败，请严格按照模板来填写内容。';
            return json(['status' => 0, 'msg' => $msg]);
        }

        return json(['status' => 0, 'msg' => $msg]);
    }

    /**
     * 导出数据
     */
    public static function export($request){
        $param = $request->param();

        // ++++++++++ 表头 Begin ++++++++++ //
        $xlsTitle = '政策享受明细';
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $spreadsheet->getActiveSheet()->setTitle($xlsTitle);

        $spreadsheet->getActiveSheet()->setCellValue('A1', '政策享受明细');
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

        $policyledgerInfo = Povertypolicyledger::where(['id' => $param['ledgerId']])->find();
        $policyList = json_decode($policyledgerInfo['policy'], true);

        $spreadsheet->getActiveSheet()->setCellValue('A2', '所属镇');
        $spreadsheet->getActiveSheet()->setCellValue('B2', '所属村');
        $spreadsheet->getActiveSheet()->setCellValue('C2', '户主名');
        $spreadsheet->getActiveSheet()->setCellValue('D2', '贫困户名');
        $spreadsheet->getActiveSheet()->setCellValue('E2', '享受政策年度');
        // 合并列
        $spreadsheet->getActiveSheet()->mergeCells('A2:A3');
        $spreadsheet->getActiveSheet()->mergeCells('B2:B3');
        $spreadsheet->getActiveSheet()->mergeCells('C2:C3');
        $spreadsheet->getActiveSheet()->mergeCells('D2:D3');
        $spreadsheet->getActiveSheet()->mergeCells('E2:E3');
        // 居中显示
        $spreadsheet->getActiveSheet()->getStyle('A2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $spreadsheet->getActiveSheet()->getStyle('B2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $spreadsheet->getActiveSheet()->getStyle('C2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $spreadsheet->getActiveSheet()->getStyle('D2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        $spreadsheet->getActiveSheet()->getStyle('E2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);
        //$i = 5;
        $j = 5;
        foreach ($policyList as $policy) {
            if(count($policy['policySpec']) > 1){
                // 多列
                $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '2', show_policys($policy['policyType']));
                // 合并行
                $spreadsheet->getActiveSheet()->mergeCells(intToChr($j) . '2:' . intToChr($j + count($policy['policySpec']) - 1) . '2');
                // 居中显示
                $spreadsheet->getActiveSheet()->getStyle(intToChr($j) . '2')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                    ]
                ]);
                foreach($policy['policySpec'] as $policySpec){
                    switch($policySpec['specType']){
                        case 1:
                            $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '3', show_quarter($policySpec['quarter']));
                            break;
                        case 2:
                            $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '3', show_month($policySpec['month']));
                            break;
                        default: break;
                    }
                    $j ++;
                }
            }else{
                // 一列
                $policySpec = $policy['policySpec'][0];
                if($policySpec['specType'] != 0){
                    switch($policySpec['specType']){
                        case 1:
                            $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '3', show_quarter($policySpec['quarter']));
                            break;
                        case 2:
                            $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '3', show_month($policySpec['month']));
                            break;
                        default: break;
                    }
                }else{
                    // 合并列
                    $spreadsheet->getActiveSheet()->mergeCells(intToChr($j) .'2:'. intToChr($j) .'3');
                    // 居中显示
                    $spreadsheet->getActiveSheet()->getStyle(intToChr($j) . '2')->applyFromArray([
                        'alignment' => [
                            'horizontal' => Alignment::HORIZONTAL_CENTER,
                            'vertical' => Alignment::VERTICAL_CENTER
                        ]
                    ]);
                }
                $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '2', show_policys($policy['policyType']));
                $j ++;
            }
            //$i ++;
        }
        $spreadsheet->getActiveSheet()->setCellValue(intToChr($j) . '2', '备注');
        // 合并列
        $spreadsheet->getActiveSheet()->mergeCells(intToChr($j) .'2:'. intToChr($j) .'3');
        // 居中显示
        $spreadsheet->getActiveSheet()->getStyle(intToChr($j) .'2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ]
        ]);

        // 合并
        $spreadsheet->getActiveSheet()->mergeCells('A1:' . intToChr($j). '1');
        // 将A1单元格设置为水平居中对齐
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
        // ++++++++++ 表头 End ++++++++++ //

        // 表格数据
        $where = [
            'policyledgerId' => $param['ledgerId']
        ];
        if(!empty($param['cityId'])){
            $where['cityId'] = $param['cityId'];
        }
        if(!empty($param['xianId'])){
            $where['xianId'] = $param['xianId'];
        }
        if(!empty($param['townId'])){
            $where['townId'] = $param['townId'];
        }
        if(!empty($param['villageId'])){
            $where['villageId'] = $param['villageId'];
        }
        if(!empty($param['name'])){
            $where['name'] = ['like', '%'.$param['name'].'%'];
        }

        $model = model('Povertypolicy');
        $data = $model->where($where)->order('householderId ASC')->select();
        if(count($data) <= 0){
            return [
                'code' => 2,
                'msg' => '没有数据！'
            ];
        }

        // Table 内容
        $cellName = [];
        $cellName['A'] = ['townId'];
        $cellName['B'] = ['villageId'];
        $cellName['C'] = ['householderId'];
        $cellName['D'] = ['memberId'];
        $cellName['E'] = ['policyledgerYear'];
        $i2 = 5;
        $j2 = 0;
        foreach ($policyList as $policy2) {
            if(count($policy2['policySpec']) > 1){
                // 多列
                foreach($policy2['policySpec'] as $policySpec2){
                    $cellName[intToChr($i2)] = ["explainValue_$j2"];
                    $i2 ++;
                    $j2 ++;
                }
            }else{
                // 一列
                $cellName[intToChr($i2)] = ["explainValue_$j2"];
                $i2 ++;
                $j2 ++;
            }
        }
        $cellName[intToChr($i2)] = ['remark'];

        $areaDB = db('area');
        $villageDB = db('villages');
        if($data) {
            $index1 = 4;
            foreach ($data as $key => $item) {
                // 所属镇
                $item['townId'] = $areaDB->where(['id' => $item['townId']])->value('name');
                // 所属村
                $item['villageId'] = $villageDB->where(['id' => $item['villageId']])->value('name');
                // 户主
                $item['householderId'] = Member::where(['id' => $item['householderId']])->value('name');
                // 贫困户
                $item['memberId'] = Member::where(['id' => $item['memberId']])->value('name');
                // 享受政策年度
                $item['policyledgerYear'] = $policyledgerInfo['year'] . '年度';
                // 政策享受
                $policyList = json_decode($item['policy'], true);
                $index = 0;
                foreach ($policyList as $policyKey => $policy) {
                    if (count($policy['policySpec']) > 1) {
                        // 多列
                        foreach ($policy['policySpec'] as $policySpec) {
                            $item["explainValue_$index"] = $policySpec['explainValue'];
                            $index++;
                        }
                    } else {
                        // 一列
                        $policySpec = $policy['policySpec'][0];
                        $item["explainValue_$index"] = $policySpec['explainValue'];
                        $index++;
                    }
                }

                foreach ($cellName as $key1 => $cell1) {
                    // 解决长数字串显示为科学计数（1.23E+12）
                    $spreadsheet->getActiveSheet()->setCellValueExplicit($key1 . ($index1), $item[$cell1[0]],DataType::TYPE_STRING);
                }

                // 每执行1000条数据
                if($index1 % 1000 == 0){
                    sleep(1);
                }
                $index1 ++;
            }
            // 添加边框
            $styleArray2 = [
                'borders' => [
                    'outline' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => '000000'],
                    ],
                ],
            ];
            $spreadsheet->getActiveSheet()->getStyle('A2:'. intToChr($i2). ($index1 - 1))->applyFromArray($styleArray2);

            $filePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'csv' . DS . 'povertypolicy' . DS . date('Y-m-d');
            $fileName = $filePath . '/povertypolicy_' . date('y-m-d-H-i-s');

            //检查文件或目录是否存在
            if(!file_exists($filePath)){
                mkdir($filePath, 0777, true);
            }
            $writer = new Xlsx($spreadsheet);
            $writer->save($fileName . '.xls'); // or .xlsx

            $xlsTitle = iconv('utf-8', 'gb2312', $xlsTitle); // 文件名称
            $downFileName = basename($fileName,".".pathinfo($fileName, PATHINFO_EXTENSION));
            header('pragma:public');
            header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
            header("Content-Disposition:attachment;filename=$downFileName.xls"); // attachment 新窗口打印,inline 本窗口打印

            $writer->save('php://output');
        }
        exit;
    }

}